﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.IO;
using System.Text;
using WdpaDownloadServiceRef;

public partial class Default2 : System.Web.UI.Page
{
    string m_LogFilePath;

    //Logging m_myLogging;

    string strSearchType;
    string strGenCountryQry;
    string strGenMPAQry;
    string strNatDesignationQry;
    string strNatDesignationptQry;
    string strNatIUCNCatQry;
    string strNatIUCNCatptQry;
    string strIntConventionQry;
    string strIntConventionptQry;
    //string strIntCriteriaQry = Convert.ToString(Session["IntCriteriaID"]);
    string strGenDTMA1;
    string strGenDTMA2;
    string strGenMarineComponentSub;
    string strGenMarineComponentSubpt;
    string strGenMarineComponentInt;
    string strGenMarineComponentIntpt;
    string strGenNoTakeReg;
    string strGenNoTakeRegpt;
    string strMarine;
    //string dbandUser;
    string strPolygonDB;
    string strPointDB;

    private string m_strAreTheyProteus;



    /// <summary>
    /// On the first page load "(if !Page.IsPostBack)" the left menu is removed and the nav bar is highlighted
    /// on the 'search' field.
    /// A check is performed to see if the website is being viewed from the wdpa-marine.org domain.  If it is,
    /// the session marine is set to active, allowing changes to be made later in the code to modify SQL querystrings.
    /// A check is also made to see if the current user has logged in.  If they have, the download div will be visible.
    /// Finally, the search type from multiselect.aspx is determined (National or International) from a session varible.
    /// If this session variable is empty, the user is redirected to multiselect.aspx.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Page_Load(object sender, EventArgs e)
    {
        SetSessionsToVariables();

        if (!Page.IsPostBack)
        {

            HtmlControl MenuSearch = (HtmlControl)Master.FindControl("MenuSearch");
            MenuSearch.Attributes.Add("class", "active");

            HtmlControl aSearch = (HtmlControl)Master.FindControl("aSearch");
            aSearch.Attributes.Add("class", "active");
        }


        if (strMarine == "")
        {
            //Gets the IP Address if needed
            string strHost = Convert.ToString(Request.ServerVariables["LOCAL_ADDR"]);

            //Gets the domain name in www.*****.org format
            string strHost2 = Convert.ToString(Request.Url.Host);

            if (strHost2 == "www.wdpa-marine.org" || strHost2 == "www.wdpa-marine.org/wdpadev/")
            {
                Session["Marine"] = "Active";
            }
        }

        //Todo:
        m_LogFilePath = Convert.ToString(Session["LogFilePath"]);

        if (User.Identity.IsAuthenticated)
        {
            DownloadSection.Visible = true;
            //Read the T and C's from the text file and output them into a div
            // Note: File.ReadAll needs to use system.IO
            string mapTandCFilePath = Server.MapPath("") + "/TandC.txt"; ;
            string readTandC = File.ReadAllText(mapTandCFilePath);

            scroll_box.InnerHtml = readTandC;

            string strEmail = User.Identity.Name;

          
        }



        if (strSearchType == "National")
        {
            RunNationalCode();
            //if (!Page.IsPostBack)
            //{
            //    CreateWhereClause("", ref downloadSB, true, strMarine);
            //}
        }

        else if (strSearchType == "International")
        {
            RunInternationalCode();
            //if (!Page.IsPostBack)
            //{
            //    CreateWhereClause("", ref downloadSB, false, strMarine);
            //}
        }

        else if (strSearchType == "")
        {
            Response.Redirect("MultiSelect.aspx");
        }

        //if (m_myLogging != null)
        //{
        //    m_myLogging.WriteToLog(false);
        //}

        //Session["mydownloadString"] = downloadSB.ToString();
    }

    /// <summary>
    /// The following function 
    /// </summary>
    protected void SetSessionsToVariables()
    {
        strSearchType = Convert.ToString(Session["SearchType"]);
        strGenCountryQry = Convert.ToString(Session["GenCountryID"]);
        strGenMPAQry = Convert.ToString(Session["GenMPAID"]);
        strNatDesignationQry = Convert.ToString(Session["NatDesignationID"]);
        strNatDesignationptQry = Convert.ToString(Session["NatDesignationptID"]);
        strNatIUCNCatQry = Convert.ToString(Session["NatIUCNCatID"]);
        strNatIUCNCatptQry = Convert.ToString(Session["NatIUCNCatptID"]);
        strIntConventionQry = Convert.ToString(Session["IntConventionID"]);
        strIntConventionptQry = Convert.ToString(Session["IntConventionptID"]);
        //string strIntCriteriaQry = Convert.ToString(Session["IntCriteriaID"]);
        strGenDTMA1 = Convert.ToString(Session["GenDTMA1"]);
        strGenDTMA2 = Convert.ToString(Session["GenDTMA2"]);
        strGenMarineComponentSub = Convert.ToString(Session["GenMarineComponentSub"]);
        strGenMarineComponentSubpt = Convert.ToString(Session["GenMarineComponentSubpt"]);
        strGenMarineComponentInt = Convert.ToString(Session["GenMarineComponentInt"]);
        strGenMarineComponentIntpt = Convert.ToString(Session["GenMarineComponentIntpt"]);
        strGenNoTakeReg = Convert.ToString(Session["GenNoTakeReg"]);
        strGenNoTakeRegpt = Convert.ToString(Session["GenNoTakeRegpt"]);
        strMarine = Convert.ToString(Session["Marine"]);
        //dbandUser = CodeConfig.DatabaseAndUser;
        strPolygonDB = CodeConfig.PolygonDB;
        strPointDB = CodeConfig.PointDB;
    }

    protected void RunNationalCode()
    {
        divNatResults.Visible = true;
        divIntResults.Visible = false;
        if (!Page.IsPostBack)
        {
            LTitle.Text = "Multiple Site Results: National";
            GVNatResults.DataSourceID = null;

            StringBuilder SBSQL1 = new StringBuilder();

            SBSQL1.Append("SELECT DISTINCT " + strPolygonDB + ".Site_ID, " + strPolygonDB + ".English_Name, ");
            SBSQL1.Append("DesignationNew.WCMCDescription AS Designation, Country.ShortName AS Country, ");
            SBSQL1.Append(strPolygonDB + ".IUCNCategory, " + strPolygonDB + ".MarineTerrestrial");
            SBSQL1.Append(", CAST(" + strPolygonDB + ".DocumentedTotalArea AS Decimal(18, 2)) AS DocumentedTotalArea, CAST (" + strPolygonDB + ".DocumentedTotalMarineArea AS Decimal(18, 2)) AS DocumentedTotalMarineArea");
            SBSQL1.Append(" FROM " + strPolygonDB + " INNER JOIN ");
            SBSQL1.Append("DesignationNew ON " + strPolygonDB + ".Local_Designation = DesignationNew.ID INNER JOIN ");
            SBSQL1.Append("Country ON " + strPolygonDB + ".Country = Country.ID ");
            if (strMarine == "Active")
            {
                SBSQL1.Append("INNER JOIN NoTakeEntity2 ON " + strPolygonDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL1.Append("WHERE (" + strPolygonDB + ".English_Name <> '') ");
            SBSQL1.Append("AND (" + strPolygonDB + ".Country " + strGenCountryQry);
            SBSQL1.Append("AND (" + strPolygonDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL1.Append("AND (" + strPolygonDB + ".Local_Designation " + strNatDesignationQry);
            SBSQL1.Append("AND (" + strPolygonDB + ".IUCNCategory " + strNatIUCNCatQry);
            if (strMarine == "Active")
            {
                SBSQL1.Append("AND ((" + strPolygonDB + ".DocumentedTotalMarineArea " + strGenDTMA1);
                SBSQL1.Append("AND (" + strPolygonDB + ".Subtidal " + strGenMarineComponentSub);
                SBSQL1.Append("AND (" + strPolygonDB + ".Intertidal " + strGenMarineComponentInt);
                SBSQL1.Append("AND (" + strPolygonDB + ".NoTakeProportion " + strGenNoTakeReg);
            }
            SBSQL1.Append("AND (" + strPolygonDB + ".SiteType = '1') ");
            SBSQL1.Append("UNION ");
            SBSQL1.Append("SELECT DISTINCT " + strPointDB + ".Site_ID, " + strPointDB + ".English_Name, ");
            SBSQL1.Append("Designation_1.WCMCDescription AS Designation, Country_1.ShortName AS Country, ");
            SBSQL1.Append(strPointDB + ".IUCNCategory, " + strPointDB + ".MarineTerrestrial");
            SBSQL1.Append(", CAST(" + strPointDB + ".DocumentedTotalArea AS Decimal(18, 2)) AS DocumentedTotalArea, CAST(" + strPointDB + ".DocumentedTotalMarineArea AS Decimal(18, 2)) AS DocumentedTotalMarineArea");
            SBSQL1.Append(" FROM " + strPointDB + " INNER JOIN ");
            SBSQL1.Append("DesignationNew AS Designation_1 ON " + strPointDB + ".Local_Designation = Designation_1.ID ");
            SBSQL1.Append("INNER JOIN Country AS Country_1 ON " + strPointDB + ".Country = Country_1.ID ");
            if (strMarine == "Active")
            {
                SBSQL1.Append("INNER JOIN NoTakeEntity2 ON " + strPointDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL1.Append("WHERE (" + strPointDB + ".English_Name <> '') ");
            SBSQL1.Append("AND (" + strPointDB + ".Country " + strGenCountryQry);
            SBSQL1.Append("AND (" + strPointDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL1.Append("AND (" + strPointDB + ".Local_Designation " + strNatDesignationptQry);
            SBSQL1.Append("AND (" + strPointDB + ".IUCNCategory " + strNatIUCNCatptQry);
            if (strMarine == "Active")
            {
                SBSQL1.Append("AND ((" + strPointDB + ".DocumentedTotalMarineArea " + strGenDTMA2);
                SBSQL1.Append("AND (" + strPointDB + ".Subtidal " + strGenMarineComponentSubpt);
                SBSQL1.Append("AND (" + strPointDB + ".Intertidal " + strGenMarineComponentIntpt);
                SBSQL1.Append("AND (" + strPointDB + ".NoTakeProportion " + strGenNoTakeRegpt);
            }
            SBSQL1.Append("AND (" + strPointDB + ".SiteType = '1') ");
            SBSQL1.Append("ORDER BY " + strPolygonDB + ".English_Name");

            string strSBSQL1 = Convert.ToString(SBSQL1);

            SqlDataSource1.SelectCommand = strSBSQL1;

            //m_myLogging = new Logging(strSBSQL1, m_LogFilePath);

            Session["NatAlphaAll"] = Convert.ToString(SqlDataSource1.SelectCommand);

            Session["ActiveNatSql"] = "All";

            GVNatResults.DataSourceID = "SqlDataSource1";

            GVNatResults.AllowPaging = false;

            GVNatResults.DataBind();

            int countNatResults = GVNatResults.Rows.Count;

            LRecordsCount.Text = "Your search has returned " + Convert.ToString(countNatResults) + " nationally designated protected areas.";

            GVNatResults.AllowPaging = true;

            string strRemoveSpecialCharacters1 = "AND (" + strPolygonDB + ".English_Name NOT LIKE '''%') AND (" + strPolygonDB + ".English_Name NOT LIKE ' %') AND (" + strPolygonDB + ".English_Name NOT LIKE '(%') AND (" + strPolygonDB + ".English_Name NOT LIKE '$\"%') AND (" + strPolygonDB + ".English_Name NOT LIKE '#_%' ESCAPE '#') ";
            string strRemoveSpecialCharacters2 = "AND (" + strPointDB + ".English_Name NOT LIKE '''%') AND (" + strPointDB + ".English_Name NOT LIKE ' %') AND (" + strPointDB + ".English_Name NOT LIKE '(%') AND (" + strPointDB + ".English_Name NOT LIKE '$\"%') AND (" + strPointDB + ".English_Name NOT LIKE '#_%' ESCAPE '#') ";

            StringBuilder SBSQL3 = new StringBuilder();

            SBSQL3.Append("SELECT DISTINCT LEFT(" + strPolygonDB + ".English_Name, 1) AS Alpha ");
            SBSQL3.Append("FROM " + strPolygonDB + " INNER JOIN ");
            SBSQL3.Append("DesignationNew ON " + strPolygonDB + ".Local_Designation = DesignationNew.ID INNER JOIN ");
            SBSQL3.Append("Country ON " + strPolygonDB + ".Country = Country.ID ");
            if (strMarine == "Active")
            {
                SBSQL3.Append("INNER JOIN NoTakeEntity2 ON " + strPolygonDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL3.Append("WHERE (" + strPolygonDB + ".English_Name <> '') ");
            SBSQL3.Append("AND (" + strPolygonDB + ".Country " + strGenCountryQry);
            SBSQL3.Append("AND (" + strPolygonDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL3.Append("AND (" + strPolygonDB + ".Local_Designation " + strNatDesignationQry);
            SBSQL3.Append("AND (" + strPolygonDB + ".IUCNCategory " + strNatIUCNCatQry);
            if (strMarine == "Active")
            {
                SBSQL3.Append("AND ((" + strPolygonDB + ".DocumentedTotalMarineArea " + strGenDTMA1);
                SBSQL3.Append("AND (" + strPolygonDB + ".Subtidal " + strGenMarineComponentSub);
                SBSQL3.Append("AND (" + strPolygonDB + ".Intertidal " + strGenMarineComponentInt);
                SBSQL3.Append("AND (" + strPolygonDB + ".NoTakeProportion " + strGenNoTakeReg);
            }
            SBSQL3.Append("AND (" + strPolygonDB + ".SiteType = '1') " + strRemoveSpecialCharacters1);
            SBSQL3.Append("UNION ");
            SBSQL3.Append("SELECT DISTINCT LEFT (" + strPointDB + ".English_Name, 1) AS Alpha ");
            SBSQL3.Append("FROM " + strPointDB + " INNER JOIN ");
            SBSQL3.Append("DesignationNew AS Designation_1 ON " + strPointDB + ".Local_Designation = Designation_1.ID ");
            SBSQL3.Append("INNER JOIN Country AS Country_1 ON " + strPointDB + ".Country = Country_1.ID ");
            if (strMarine == "Active")
            {
                SBSQL3.Append("INNER JOIN NoTakeEntity2 ON " + strPointDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL3.Append("WHERE (" + strPointDB + ".English_Name <> '') ");
            SBSQL3.Append("AND (" + strPointDB + ".Country " + strGenCountryQry);
            SBSQL3.Append("AND (" + strPointDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL3.Append("AND (" + strPointDB + ".Local_Designation " + strNatDesignationptQry);
            SBSQL3.Append("AND (" + strPointDB + ".IUCNCategory " + strNatIUCNCatptQry);
            if (strMarine == "Active")
            {
                SBSQL3.Append("AND ((" + strPointDB + ".DocumentedTotalMarineArea " + strGenDTMA2);
                SBSQL3.Append("AND (" + strPointDB + ".Subtidal " + strGenMarineComponentSubpt);
                SBSQL3.Append("AND (" + strPointDB + ".Intertidal " + strGenMarineComponentIntpt);
                SBSQL3.Append("AND (" + strPointDB + ".NoTakeProportion " + strGenNoTakeRegpt);
            }
            SBSQL3.Append("AND (" + strPointDB + ".SiteType = '1') " + strRemoveSpecialCharacters2);
            SBSQL3.Append("ORDER BY Alpha");

            string strSBSQL3 = Convert.ToString(SBSQL3);

            SqlDataSource3.SelectCommand = strSBSQL3;

            String dbConn = ConfigurationManager.ConnectionStrings["WDPAConnectionString1"].ConnectionString;
            System.Data.SqlClient.SqlDataReader FindNatResults;
            System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(dbConn);
            sqlConn.Open();

            string query = SqlDataSource1.SelectCommand;
            System.Data.SqlClient.SqlCommand imageCommand = new System.Data.SqlClient.SqlCommand(query, sqlConn);
            FindNatResults = imageCommand.ExecuteReader(CommandBehavior.CloseConnection);

            if (!FindNatResults.HasRows)
            {
                DownloadSection.Visible = false;
            }
        }
        else if (Page.IsPostBack)
        {
            GVNatResults.DataSourceID = null;
            string strActiveNatSql = Convert.ToString(Session["ActiveNatSql"]);

            if (strActiveNatSql == "Alpha")
            {
                string strNatAlphaData = Convert.ToString(Session["NatAlphaData"]);
                SqlDataSource1.SelectCommand = strNatAlphaData;
            }
            else
            {
                string strNatAlphaAll = Convert.ToString(Session["NatAlphaAll"]);
                SqlDataSource1.SelectCommand = strNatAlphaAll;
            }
            GVNatResults.DataSourceID = "SqlDataSource1";
            GVNatResults.DataBind();
        }
    }

    protected void RunInternationalCode()
    {
        divNatResults.Visible = false;
        divIntResults.Visible = true;
        if (!Page.IsPostBack)
        {
            LTitle.Text = "Multiple Site Results: International";

            GVIntResults.DataSourceID = null;

            StringBuilder SBSQL2 = new StringBuilder();

            SBSQL2.Append("SELECT DISTINCT " + strPolygonDB + ".Site_ID, " + strPolygonDB + ".English_Name, IRConventionEntity.NAME AS Convention, ");
            SBSQL2.Append("Country.ShortName AS Country, ");
            //SBSQL2.Append("Criteria.Description AS Criteria, "); 
            SBSQL2.Append(strPolygonDB + ".MarineTerrestrial");
            SBSQL2.Append(", CAST(" + strPolygonDB + ".DocumentedTotalArea AS Decimal(18, 2)) AS DocumentedTotalArea, CAST(" + strPolygonDB + ".DocumentedTotalMarineArea AS Decimal(18, 2)) AS DocumentedTotalMarineArea");
            SBSQL2.Append(" FROM " + strPolygonDB + " INNER JOIN ");
            SBSQL2.Append("IRConventionEntity ON " + strPolygonDB + ".International_Convention = IRConventionEntity.ID INNER JOIN ");
            SBSQL2.Append("Country ON " + strPolygonDB + ".Country = Country.ID ");
            //SBSQL2.Append("INNER JOIN Criteria ON " + strPolygonDB + ".Criteria = Criteria.ID ");
            if (strMarine == "Active")
            {
                SBSQL2.Append("INNER JOIN NoTakeEntity2 ON " + strPolygonDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL2.Append("WHERE (" + strPolygonDB + ".English_Name <> '') ");
            SBSQL2.Append("AND (" + strPolygonDB + ".Country " + strGenCountryQry);
            SBSQL2.Append("AND (" + strPolygonDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL2.Append("AND (" + strPolygonDB + ".International_Convention " + strIntConventionQry);
            //SBSQL2.Append("AND (" + strPolygonDB + ".Criteria " + strIntCriteriaQry);
            if (strMarine == "Active")
            {
                SBSQL2.Append("AND ((" + strPolygonDB + ".DocumentedTotalMarineArea " + strGenDTMA1);
                SBSQL2.Append("AND (" + strPolygonDB + ".Subtidal " + strGenMarineComponentSub);
                SBSQL2.Append("AND (" + strPolygonDB + ".Intertidal " + strGenMarineComponentInt);
                SBSQL2.Append("AND (" + strPolygonDB + ".NoTakeProportion " + strGenNoTakeReg);
            }
            SBSQL2.Append("AND (" + strPolygonDB + ".SiteType = '2') ");
            SBSQL2.Append("UNION ");
            SBSQL2.Append("SELECT DISTINCT " + strPointDB + ".Site_ID, " + strPointDB + ".English_Name, IRConventionEntity.NAME AS Convention, ");
            SBSQL2.Append("Country.ShortName AS Country, ");
            //SBSQL2.Append("Criteria.Description AS Criteria, ");
            SBSQL2.Append(strPointDB + ".MarineTerrestrial");
            SBSQL2.Append(", CAST(" + strPointDB + ".DocumentedTotalArea AS Decimal(18, 2)) AS DocumentedTotalArea, CAST(" + strPointDB + ".DocumentedTotalMarineArea AS Decimal(18, 2)) AS DocumentedTotalMarineArea");
            SBSQL2.Append(" FROM " + strPointDB + " INNER JOIN ");
            SBSQL2.Append("IRConventionEntity ON " + strPointDB + ".International_Convention = IRConventionEntity.ID INNER JOIN ");
            SBSQL2.Append("Country ON " + strPointDB + ".Country = Country.ID ");
            //SBSQL2.Append("INNER JOIN Criteria ON " + strPointDB + ".Criteria = Criteria.ID ");
            if (strMarine == "Active")
            {
                SBSQL2.Append("INNER JOIN NoTakeEntity2 ON " + strPointDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL2.Append("WHERE (" + strPointDB + ".English_Name <> '') ");
            SBSQL2.Append("AND (" + strPointDB + ".Country " + strGenCountryQry);
            SBSQL2.Append("AND (" + strPointDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL2.Append("AND (" + strPointDB + ".International_Convention " + strIntConventionptQry);
            //SBSQL2.Append("AND (" + strPointDB + ".Criteria " + strIntCriteriaQry);
            if (strMarine == "Active")
            {
                SBSQL2.Append("AND ((" + strPointDB + ".DocumentedTotalMarineArea " + strGenDTMA2);
                SBSQL2.Append("AND (" + strPointDB + ".Subtidal " + strGenMarineComponentSubpt);
                SBSQL2.Append("AND (" + strPointDB + ".Intertidal " + strGenMarineComponentIntpt);
                SBSQL2.Append("AND (" + strPointDB + ".NoTakeProportion " + strGenNoTakeRegpt);
            }
            SBSQL2.Append("AND (" + strPointDB + ".SiteType = '2') ");
            SBSQL2.Append("ORDER BY " + strPolygonDB + ".English_Name");

            string strSBSQL2 = Convert.ToString(SBSQL2);

            SqlDataSource2.SelectCommand = strSBSQL2;

            //m_myLogging = new Logging(strSBSQL2, m_LogFilePath);

            Session["IntAlphaAll"] = Convert.ToString(SqlDataSource2.SelectCommand);

            Session["ActiveIntSql"] = "All";

            GVIntResults.DataSourceID = "SqlDataSource2";

            GVIntResults.AllowPaging = false;

            GVIntResults.DataBind();

            int countIntResults = GVIntResults.Rows.Count;

            LRecordsCount.Text = "Your search has returned " + Convert.ToString(countIntResults) + " internationally recognized protected areas.";

            GVIntResults.AllowPaging = true;

            string strRemoveSpecialCharacters1 = "AND (" + strPolygonDB + ".English_Name NOT LIKE '''%') AND (" + strPolygonDB + ".English_Name NOT LIKE ' %') AND (" + strPolygonDB + ".English_Name NOT LIKE '(%') AND (" + strPolygonDB + ".English_Name NOT LIKE '$\"%') AND (" + strPolygonDB + ".English_Name NOT LIKE '#_%' ESCAPE '#') ";
            string strRemoveSpecialCharacters2 = "AND (" + strPointDB + ".English_Name NOT LIKE '''%') AND (" + strPointDB + ".English_Name NOT LIKE ' %') AND (" + strPointDB + ".English_Name NOT LIKE '(%') AND (" + strPointDB + ".English_Name NOT LIKE '$\"%') AND (" + strPointDB + ".English_Name NOT LIKE '#_%' ESCAPE '#') ";


            StringBuilder SBSQL3 = new StringBuilder();

            SBSQL3.Append("SELECT DISTINCT LEFT(" + strPolygonDB + ".English_Name, 1) AS Alpha ");
            SBSQL3.Append("FROM " + strPolygonDB + " INNER JOIN ");
            SBSQL3.Append("IRConventionEntity ON " + strPolygonDB + ".International_Convention = IRConventionEntity.ID INNER JOIN ");
            SBSQL3.Append("Country ON " + strPolygonDB + ".Country = Country.ID ");
            //SBSQL3.Append("INNER JOIN ");
            //SBSQL3.Append("Criteria ON " + strPolygonDB + ".Criteria = Criteria.ID ");
            if (strMarine == "Active")
            {
                SBSQL3.Append("INNER JOIN NoTakeEntity2 ON " + strPolygonDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL3.Append("WHERE (" + strPolygonDB + ".English_Name <> '') ");
            SBSQL3.Append("AND (" + strPolygonDB + ".Country " + strGenCountryQry);
            SBSQL3.Append("AND (" + strPolygonDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL3.Append("AND (" + strPolygonDB + ".International_Convention " + strIntConventionQry);
            //SBSQL3.Append("AND (" + strPolygonDB + ".Criteria " + strIntCriteriaQry);
            if (strMarine == "Active")
            {
                SBSQL3.Append("AND ((" + strPolygonDB + ".DocumentedTotalMarineArea " + strGenDTMA1);
                SBSQL3.Append("AND (" + strPolygonDB + ".Subtidal " + strGenMarineComponentSub);
                SBSQL3.Append("AND (" + strPolygonDB + ".Intertidal " + strGenMarineComponentInt);
                SBSQL3.Append("AND (" + strPolygonDB + ".NoTakeProportion " + strGenNoTakeReg);
            }
            SBSQL3.Append("AND (" + strPolygonDB + ".SiteType = '2') " + strRemoveSpecialCharacters1);
            SBSQL3.Append("UNION ");
            SBSQL3.Append("SELECT DISTINCT LEFT (" + strPointDB + ".English_Name, 1) AS Alpha ");
            SBSQL3.Append("FROM " + strPointDB + " INNER JOIN ");
            SBSQL3.Append("IRConventionEntity ON " + strPointDB + ".International_Convention = IRConventionEntity.ID INNER JOIN ");
            SBSQL3.Append("Country ON " + strPointDB + ".Country = Country.ID ");
            //SBSQL3.Append("INNER JOIN ");
            //SBSQL3.Append("Criteria ON " + strPointDB + ".Criteria = Criteria.ID ");
            if (strMarine == "Active")
            {
                SBSQL3.Append("INNER JOIN NoTakeEntity2 ON " + strPointDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL3.Append("WHERE (" + strPointDB + ".English_Name <> '') ");
            SBSQL3.Append("AND (" + strPointDB + ".Country " + strGenCountryQry);
            SBSQL3.Append("AND (" + strPointDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL3.Append("AND (" + strPointDB + ".International_Convention " + strIntConventionptQry);
            //SBSQL3.Append("AND (" + strPointDB + ".Criteria " + strIntCriteriaQry);
            if (strMarine == "Active")
            {
                SBSQL3.Append("AND ((" + strPointDB + ".DocumentedTotalMarineArea " + strGenDTMA2);
                SBSQL3.Append("AND (" + strPointDB + ".Subtidal " + strGenMarineComponentSubpt);
                SBSQL3.Append("AND (" + strPointDB + ".Intertidal " + strGenMarineComponentIntpt);
                SBSQL3.Append("AND (" + strPointDB + ".NoTakeProportion " + strGenNoTakeRegpt);
            }
            SBSQL3.Append("AND (" + strPointDB + ".SiteType = '2') " + strRemoveSpecialCharacters2);
            SBSQL3.Append("ORDER BY Alpha");
            string strSBSQL3 = Convert.ToString(SBSQL3);

            SqlDataSource3.SelectCommand = strSBSQL3;

            String dbConn = ConfigurationManager.ConnectionStrings["WDPAConnectionString1"].ConnectionString;
            System.Data.SqlClient.SqlDataReader FindIntResults;
            System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(dbConn);
            sqlConn.Open();

            string query = SqlDataSource2.SelectCommand;
            System.Data.SqlClient.SqlCommand imageCommand = new System.Data.SqlClient.SqlCommand(query, sqlConn);
            FindIntResults = imageCommand.ExecuteReader(CommandBehavior.CloseConnection);

            if (!FindIntResults.HasRows)
            {
                DownloadSection.Visible = false;
            }
        }
        else if (Page.IsPostBack)
        {
            GVIntResults.DataSourceID = null;
            string strActiveIntSql = Convert.ToString(Session["ActiveIntSql"]);

            if (strActiveIntSql == "Alpha")
            {
                string strIntAlphaData = Convert.ToString(Session["IntAlphaData"]);
                SqlDataSource2.SelectCommand = strIntAlphaData;
            }
            else
            {
                string strIntAlphaAll = Convert.ToString(Session["IntAlphaAll"]);
                SqlDataSource2.SelectCommand = strIntAlphaAll;
            }
            GVIntResults.DataSourceID = "SqlDataSource2";
            GVIntResults.DataBind();
        }
    }

    /// <summary>
    /// Test Summary one
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void LBAlpha_Command(Object sender, CommandEventArgs e)
    {
        if (e.CommandName == "NatAlphaChoice")
        {
            GVNatResults.AllowPaging = false;

            GVNatResults.DataSourceID = null;

            StringBuilder SBSQL1 = new StringBuilder();

            SBSQL1.Append("SELECT DISTINCT " + strPolygonDB + ".Site_ID, " + strPolygonDB + ".English_Name, ");
            SBSQL1.Append("DesignationNew.WCMCDescription AS Designation, Country.ShortName AS Country, ");
            SBSQL1.Append(strPolygonDB + ".IUCNCategory, " + strPolygonDB + ".MarineTerrestrial");
            SBSQL1.Append(", CAST(" + strPolygonDB + ".DocumentedTotalArea AS Decimal(18, 2)) AS DocumentedTotalArea, CAST(" + strPolygonDB + ".DocumentedTotalMarineArea AS Decimal(18, 2)) AS DocumentedTotalMarineArea");
            SBSQL1.Append(" FROM " + strPolygonDB + " INNER JOIN ");
            SBSQL1.Append("DesignationNew ON " + strPolygonDB + ".Local_Designation = DesignationNew.ID INNER JOIN ");
            SBSQL1.Append("Country ON " + strPolygonDB + ".Country = Country.ID ");
            if (strMarine == "Active")
            {
                SBSQL1.Append("INNER JOIN NoTakeEntity2 ON " + strPolygonDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL1.Append("WHERE (" + strPolygonDB + ".English_Name <> '') ");
            SBSQL1.Append("AND (" + strPolygonDB + ".Country " + strGenCountryQry);
            SBSQL1.Append("AND (" + strPolygonDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL1.Append("AND (" + strPolygonDB + ".Local_Designation " + strNatDesignationQry);
            SBSQL1.Append("AND (" + strPolygonDB + ".IUCNCategory " + strNatIUCNCatQry);
            if (strMarine == "Active")
            {
                SBSQL1.Append("AND ((" + strPolygonDB + ".DocumentedTotalMarineArea " + strGenDTMA1);
                SBSQL1.Append("AND (" + strPolygonDB + ".Subtidal " + strGenMarineComponentSub);
                SBSQL1.Append("AND (" + strPolygonDB + ".Intertidal " + strGenMarineComponentInt);
                SBSQL1.Append("AND (" + strPolygonDB + ".NoTakeProportion " + strGenNoTakeReg);
            }
            SBSQL1.Append("AND (" + strPolygonDB + ".SiteType = '1') ");
            SBSQL1.Append("AND (" + strPolygonDB + ".English_Name LIKE '" + e.CommandArgument + "%') ");
            SBSQL1.Append("UNION ");
            SBSQL1.Append("SELECT DISTINCT " + strPointDB + ".Site_ID, " + strPointDB + ".English_Name, ");
            SBSQL1.Append("Designation_1.WCMCDescription AS DesignationNew, Country_1.ShortName AS Country, ");
            SBSQL1.Append(strPointDB + ".IUCNCategory, " + strPointDB + ".MarineTerrestrial");
            SBSQL1.Append(", CAST(" + strPointDB + ".DocumentedTotalArea AS Decimal(18, 2)) AS DocumentedTotalArea, CAST(" + strPointDB + ".DocumentedTotalMarineArea AS Decimal(18, 2)) AS DocumentedTotalMarineArea");
            SBSQL1.Append(" FROM " + strPointDB + " INNER JOIN ");
            SBSQL1.Append("DesignationNew AS Designation_1 ON " + strPointDB + ".Local_Designation = Designation_1.ID ");
            SBSQL1.Append("INNER JOIN Country AS Country_1 ON " + strPointDB + ".Country = Country_1.ID ");
            if (strMarine == "Active")
            {
                SBSQL1.Append("INNER JOIN NoTakeEntity2 ON " + strPointDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL1.Append("WHERE (" + strPointDB + ".English_Name <> '') ");
            SBSQL1.Append("AND (" + strPointDB + ".Country " + strGenCountryQry);
            SBSQL1.Append("AND (" + strPointDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL1.Append("AND (" + strPointDB + ".Local_Designation " + strNatDesignationptQry);
            SBSQL1.Append("AND (" + strPointDB + ".IUCNCategory " + strNatIUCNCatptQry);
            if (strMarine == "Active")
            {
                SBSQL1.Append("AND ((" + strPointDB + ".DocumentedTotalMarineArea " + strGenDTMA2);
                SBSQL1.Append("AND (" + strPointDB + ".Subtidal " + strGenMarineComponentSubpt);
                SBSQL1.Append("AND (" + strPointDB + ".Intertidal " + strGenMarineComponentIntpt);
                SBSQL1.Append("AND (" + strPointDB + ".NoTakeProportion " + strGenNoTakeRegpt);
            }
            SBSQL1.Append("AND (" + strPointDB + ".SiteType = '1') ");
            SBSQL1.Append("AND (" + strPointDB + ".English_Name LIKE '" + e.CommandArgument + "%') ");
            SBSQL1.Append("ORDER BY " + strPolygonDB + ".English_Name");

            string strSBSQL1 = Convert.ToString(SBSQL1);

            SqlDataSource1.SelectCommand = strSBSQL1;

            Session["NatAlphaData"] = Convert.ToString(SqlDataSource1.SelectCommand);

            Session["ActiveNatSql"] = "Alpha";

            GVNatResults.DataSourceID = "SqlDataSource1";

            GVNatResults.DataBind();

            GVNatResults.AllowPaging = true;
        }

        else if (e.CommandName == "NatAlphaAll")
        {
            GVNatResults.AllowPaging = false;
            GVNatResults.DataSourceID = null;
            string strNatAlphaAll = Convert.ToString(Session["NatAlphaAll"]);
            SqlDataSource1.SelectCommand = strNatAlphaAll;
            GVNatResults.DataSourceID = "SqlDataSource1";
            GVNatResults.DataBind();
            GVNatResults.AllowPaging = true;
            Session["ActiveNatSql"] = "All";
        }

        else if (e.CommandName == "IntAlphaChoice")
        {
            GVIntResults.AllowPaging = false;

            GVIntResults.DataSourceID = null;

            StringBuilder SBSQL2 = new StringBuilder();

            SBSQL2.Append("SELECT DISTINCT " + strPolygonDB + ".Site_ID, " + strPolygonDB + ".English_Name, IRConventionEntity.NAME AS Convention, ");
            SBSQL2.Append("Country.ShortName AS Country, ");
            //SBSQL2.Append("Criteria.Description AS Criteria, "); 
            SBSQL2.Append(strPolygonDB + ".MarineTerrestrial");
            SBSQL2.Append(", CAST(" + strPolygonDB + ".DocumentedTotalArea AS Decimal(18, 2)) AS DocumentedTotalArea, CAST(" + strPolygonDB + ".DocumentedTotalMarineArea AS Decimal(18, 2)) AS DocumentedTotalMarineArea");
            SBSQL2.Append(" FROM " + strPolygonDB + " INNER JOIN ");
            SBSQL2.Append("IRConventionEntity ON " + strPolygonDB + ".International_Convention = IRConventionEntity.ID INNER JOIN ");
            SBSQL2.Append("Country ON " + strPolygonDB + ".Country = Country.ID ");
            //SBSQL2.Append("INNER JOIN Criteria ON " + strPolygonDB + ".Criteria = Criteria.ID ");
            if (strMarine == "Active")
            {
                SBSQL2.Append("INNER JOIN NoTakeEntity2 ON " + strPolygonDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL2.Append("WHERE (" + strPolygonDB + ".English_Name <> '') ");
            SBSQL2.Append("AND (" + strPolygonDB + ".Country " + strGenCountryQry);
            SBSQL2.Append("AND (" + strPolygonDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL2.Append("AND (" + strPolygonDB + ".International_Convention " + strIntConventionQry);
            //SBSQL2.Append("AND (" + strPolygonDB + ".Criteria " + strIntCriteriaQry);
            if (strMarine == "Active")
            {
                SBSQL2.Append("AND ((" + strPolygonDB + ".DocumentedTotalMarineArea " + strGenDTMA1);
                SBSQL2.Append("AND (" + strPolygonDB + ".Subtidal " + strGenMarineComponentSub);
                SBSQL2.Append("AND (" + strPolygonDB + ".Intertidal " + strGenMarineComponentInt);
                SBSQL2.Append("AND (" + strPolygonDB + ".NoTakeProportion " + strGenNoTakeReg);
            }
            SBSQL2.Append("AND (" + strPolygonDB + ".SiteType = '2') ");
            SBSQL2.Append("AND (" + strPolygonDB + ".English_Name LIKE '" + e.CommandArgument + "%') ");
            SBSQL2.Append("UNION ");
            SBSQL2.Append("SELECT DISTINCT " + strPointDB + ".Site_ID, " + strPointDB + ".English_Name, IRConventionEntity.NAME AS Convention, ");
            SBSQL2.Append("Country.ShortName AS Country, ");
            //SBSQL2.Append("Criteria.Description AS Criteria, ");
            SBSQL2.Append(strPointDB + ".MarineTerrestrial");
            SBSQL2.Append(", CAST(" + strPointDB + ".DocumentedTotalArea AS Decimal(18, 2)) AS DocumentedTotalArea, CAST(" + strPointDB + ".DocumentedTotalMarineArea AS Decimal(18, 2)) AS DocumentedTotalMarineArea");
            SBSQL2.Append(" FROM " + strPointDB + " INNER JOIN ");
            SBSQL2.Append("IRConventionEntity ON " + strPointDB + ".International_Convention = IRConventionEntity.ID INNER JOIN ");
            SBSQL2.Append("Country ON " + strPointDB + ".Country = Country.ID ");
            //SBSQL2.Append("INNER JOIN Criteria ON " + strPointDB + ".Criteria = Criteria.ID ");
            if (strMarine == "Active")
            {
                SBSQL2.Append("INNER JOIN NoTakeEntity2 ON " + strPointDB + ".NoTakeProportion = NoTakeEntity2.Code ");
            }
            SBSQL2.Append("WHERE (" + strPointDB + ".English_Name <> '') ");
            SBSQL2.Append("AND (" + strPointDB + ".Country " + strGenCountryQry);
            SBSQL2.Append("AND (" + strPointDB + ".MarineTerrestrial " + strGenMPAQry);
            SBSQL2.Append("AND (" + strPointDB + ".International_Convention " + strIntConventionptQry);
            //SBSQL2.Append("AND (" + strPointDB + ".Criteria " + strIntCriteriaQry);
            if (strMarine == "Active")
            {
                SBSQL2.Append("AND ((" + strPointDB + ".DocumentedTotalMarineArea " + strGenDTMA2);
                SBSQL2.Append("AND (" + strPointDB + ".Subtidal " + strGenMarineComponentSubpt);
                SBSQL2.Append("AND (" + strPointDB + ".Intertidal " + strGenMarineComponentIntpt);
                SBSQL2.Append("AND (" + strPointDB + ".NoTakeProportion " + strGenNoTakeRegpt);
            }
            SBSQL2.Append("AND (" + strPointDB + ".SiteType = '2') ");
            SBSQL2.Append("AND (" + strPointDB + ".English_Name LIKE '" + e.CommandArgument + "%') ");
            SBSQL2.Append("ORDER BY " + strPolygonDB + ".English_Name");

            string strSBSQL2 = Convert.ToString(SBSQL2);

            SqlDataSource2.SelectCommand = strSBSQL2;

            Session["IntAlphaData"] = Convert.ToString(SqlDataSource2.SelectCommand);

            Session["ActiveIntSql"] = "Alpha";

            GVIntResults.DataSourceID = "SqlDataSource2";

            GVIntResults.DataBind();

            GVIntResults.AllowPaging = true;
        }
        else if (e.CommandName == "IntAlphaAll")
        {
            GVIntResults.AllowPaging = false;
            GVIntResults.DataSourceID = null;
            string strIntAlphaAll = Convert.ToString(Session["IntAlphaAll"]);
            SqlDataSource2.SelectCommand = strIntAlphaAll;
            GVIntResults.DataSourceID = "SqlDataSource2";
            GVIntResults.DataBind();
            GVIntResults.AllowPaging = true;
            Session["ActiveIntSql"] = "All";
        }
    }
    /// <summary>
    /// Test summary two
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void DDLAlphaPagingSize_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (strSearchType == "National")
        {
            GVNatResults.AllowPaging = false;
            GVNatResults.PageSize = Convert.ToInt16(DDLNatPagingSize.SelectedItem.Value);
            GVNatResults.PageIndex = 0;
            GVNatResults.DataSourceID = null;
            string strActiveNatSql = Convert.ToString(Session["ActiveNatSql"]);

            if (strActiveNatSql == "Alpha")
            {
                string strNatAlphaData = Convert.ToString(Session["NatAlphaData"]);
                SqlDataSource1.SelectCommand = strNatAlphaData;
            }
            else
            {
                string strNatAlphaAll = Convert.ToString(Session["NatAlphaAll"]);
                SqlDataSource1.SelectCommand = strNatAlphaAll;
            }

            GVNatResults.DataSourceID = "SqlDataSource1";
            GVNatResults.DataBind();
            GVNatResults.AllowPaging = true;
        }
        else if (strSearchType == "International")
        {
            GVIntResults.AllowPaging = false;
            GVIntResults.PageSize = Convert.ToInt16(DDLIntPagingSize.SelectedItem.Value);
            GVIntResults.PageIndex = 0;
            GVIntResults.DataSourceID = null;
            string strActiveIntSql = Convert.ToString(Session["ActiveIntSql"]);

            if (strActiveIntSql == "Alpha")
            {
                string strIntAlphaData = Convert.ToString(Session["IntAlphaData"]);
                SqlDataSource2.SelectCommand = strIntAlphaData;
            }
            else
            {
                string strIntAlphaAll = Convert.ToString(Session["IntAlphaAll"]);
                SqlDataSource2.SelectCommand = strIntAlphaAll;
            }
            GVIntResults.DataSourceID = "SqlDataSource2";
            GVIntResults.DataBind();
            GVIntResults.AllowPaging = true;
        }
    }

    protected void BtnDownloadShp_Click(object sender, EventArgs e)
    {
        //string sitequery = createSiteQuery();

        //createquery for download
        StringBuilder downloadSB = new StringBuilder();

        if (strSearchType == "National")
        {
            CreateWhereClause("", ref downloadSB, true, strMarine);
        }
        else
        {
            CreateWhereClause("", ref downloadSB, false, strMarine);
        }

        Session["mydownloadString"] = downloadSB.ToString();


        WdpaDownloadService WdpaDownloadService1 = new WdpaDownloadService();

        WdpaDownloadService1.DownloadInformation((String)Session["mydownloadString"],
                                                 @"\\wcmc-gis-02\e$\Inetpub\wwwroot\WDPADownloads\Downloads\",
                                                 User.Identity.Name.ToString());


        //CustomToolLibrary.downloadData MyRequest = new CustomToolLibrary.downloadData();
        //MyRequest.DownloadInformation((String)Session["mydownloadString"], @"\\wcmc-gis-02\e$\Inetpub\wwwroot\WDPADownloads\Downloads\");

        DownloadReady.Visible = true;
        DownloadSection.Visible = false;
    }


    /// <summary>
    /// fix to add apostrophe for query
    /// </summary>
    /// <param name="querystr"></param>

    private string addextraApostr(string querystr)
    {
        querystr = querystr.Replace("IN (", "");
        querystr = querystr.Replace("))", "");

        //querystr = querystr.Replace(")", "");
        //querystr = querystr.Replace("=", "");

        string[] mystringarray = querystr.Split(new Char[] { ',' });
        querystr = "";

        string dynstr;

        for (int i = 0; i < mystringarray.Length; i++)
        {
            dynstr = "'" + mystringarray[i] + "'";
            dynstr = dynstr.Replace(" ", "");
            if (i == 0)
            {
                querystr = "IN (" + dynstr;

            }
            else if (i == mystringarray.Length - 1)
            {
                querystr += ("," + dynstr + ")) ");
            }
            else
            {
                querystr += ("," + dynstr);
            }
        }

        if (mystringarray.Length == 1)
        {
            querystr += (")) ");
        }

        return querystr;
    }

    private string addextraApostr2(string querystr)
    {
        querystr = querystr.Replace(")", "");
        querystr = querystr.Replace("=", "");
        querystr = querystr.Replace(" ", "");

        querystr = "'" + querystr + "'";
        string qryStart = " = ";
        string qryEnd = ") ";
        querystr = qryStart + querystr + qryEnd;

        return querystr;
    }

    /// <summary>
    /// Function to create generic where clause
    /// </summary>
    /// <param name="dbAndUser"></param>
    /// <param name="myQueryStringBuilder"></param>
    /// <returns></returns>
    private void CreateWhereClause(string dbAndUser, ref StringBuilder myQueryStringBuilder, bool isitNational, string isitmarine)
    {

        if (dbAndUser != "")
        {
            dbAndUser += ".";
        }

        //universal part of the query
        //myQueryStringBuilder.Append("(" + dbAndUser + "English_Name <> '') ");
        myQueryStringBuilder.Append("(" + dbAndUser + "Country " + addextraApostr(strGenCountryQry));
        myQueryStringBuilder.Append("AND (" + dbAndUser + "MarineTerrestrial " + addextraApostr(strGenMPAQry));


        if (isitNational)
        {

            strNatDesignationptQry = strNatDesignationptQry.Replace(strPointDB + ".", "");
            strNatDesignationptQry = strNatDesignationptQry.Replace(strPolygonDB + ".", "");
            strNatDesignationptQry = strNatDesignationptQry.Replace("'%'", "''%''");

            myQueryStringBuilder.Append("AND (" + dbAndUser + "Local_Designation " + strNatDesignationptQry);

            if (strNatIUCNCatptQry.IndexOf("IN ") != -1)
            {
                myQueryStringBuilder.Append("AND (" + dbAndUser + "IUCNCategory " + addextraApostr(strNatIUCNCatptQry));
            }

        }
        else
        {
            strIntConventionQry = strIntConventionQry.Replace(strPointDB + ".", "");
            strIntConventionQry = strIntConventionQry.Replace(strPolygonDB + ".", "");
            strIntConventionQry = strIntConventionQry.Replace("'%'", "''%''");

            myQueryStringBuilder.Append("AND (" + dbAndUser + "International_Convention " + strIntConventionQry);
            //SBSQL2.Append("AND (" + strPolygonDB + ".Criteria " + strIntCriteriaQry);
        }

        if (isitmarine == "Active")
        {

            strGenDTMA1 = strGenDTMA1.Replace(strPointDB + ".", "");
            strGenDTMA1 = strGenDTMA1.Replace(strPolygonDB + ".", "");
            strGenDTMA1 = strGenDTMA1.Replace("'%'", "''%''");

            strGenMarineComponentSub = strGenMarineComponentSub.Replace(strPointDB + ".", "");
            strGenMarineComponentSub = strGenMarineComponentSub.Replace(strPolygonDB + ".", "");
            strGenMarineComponentSub = strGenMarineComponentSub.Replace("'Yes'", "''Yes''");
            strGenMarineComponentSub = strGenMarineComponentSub.Replace("'%'", "''%''");

            strGenMarineComponentInt = strGenMarineComponentInt.Replace(strPointDB + ".", "");
            strGenMarineComponentInt = strGenMarineComponentInt.Replace(strPolygonDB + ".", "");
            strGenMarineComponentInt = strGenMarineComponentInt.Replace("'Yes'", "''Yes''");
            strGenMarineComponentInt = strGenMarineComponentInt.Replace("'%'", "''%''");

            strGenNoTakeReg = strGenNoTakeReg.Replace(strPointDB + ".", "");
            strGenNoTakeReg = strGenNoTakeReg.Replace(strPolygonDB + ".", "");
            strGenNoTakeReg = strGenNoTakeReg.Replace("'%'", "''%''");


            myQueryStringBuilder.Append("AND ((" + dbAndUser + "DocumentedTotalMarineArea " + strGenDTMA1);
            myQueryStringBuilder.Append("AND (" + dbAndUser + "Subtidal " + strGenMarineComponentSub);
            myQueryStringBuilder.Append("AND (" + dbAndUser + "Intertidal " + strGenMarineComponentInt);
            if (strGenNoTakeReg.IndexOf("LIKE ") != -1)
            {
                myQueryStringBuilder.Append("AND (" + dbAndUser + "NoTakeProportion " + strGenNoTakeReg);
            }
            else
            {
                myQueryStringBuilder.Append("AND (" + dbAndUser + "NoTakeProportion " + addextraApostr2(strGenNoTakeReg));
            }
        }

        if (isitNational)
        {
            myQueryStringBuilder.Append("AND (" + dbAndUser + "SiteType = ''1'') ");
        }
        else
        {
            myQueryStringBuilder.Append("AND (" + dbAndUser + "SiteType = ''2'') ");
        }


        string strTest = myQueryStringBuilder.ToString();

    }





    /// <summary>
    /// Function to construct the query which will be used to extract data from the SDE
    /// </summary>
    /// <returns></returns>
    private string createSiteQuery()
    {
        DataView sitequeryDV;
        DataSourceSelectArguments args = new DataSourceSelectArguments();

        string query = "In(";
        int first = 0;

        if (Convert.ToString(Session["SearchType"]) == "National")//todo:put stuff here
        {

            sitequeryDV = (DataView)SqlDataSource1.Select(args);

            foreach (DataRowView sitedrv in sitequeryDV)
            {
                if (!(first == 0))
                {
                    query += ",";
                    query += Convert.ToString(sitedrv["Site_ID"]);
                }
                else
                {
                    query += Convert.ToString(sitedrv["Site_ID"]);

                }

                first += 1;
            }
        }
        else
        {
            sitequeryDV = (DataView)SqlDataSource2.Select(args);

            first = 0;
            foreach (DataRowView sitedrv in sitequeryDV)
            {
                if (!(first == 0))
                {
                    query += ",";
                    query += Convert.ToString(sitedrv["Site_ID"]);
                }
                else
                {
                    //simple check to see if and results have been returned from the national filter
                    if (query != "In(")
                    {
                        query += ",";
                    }
                    query += Convert.ToString(sitedrv["Site_ID"]);

                }
                first += 1;
            }
        }

        query += ")";

        return query;
    }

    protected void AgreetoTandC_CheckedChanged1(object sender, EventArgs e)
    {
        if (AgreetoTandC.Checked)
        {
            downloadSubmit.Enabled = true;
        }
        else
        {
            downloadSubmit.Enabled = false;
        }
    }

    protected void btnGoToDownloads_Click(Object sender, EventArgs e)
    {
        Response.Redirect("Download.aspx");
    }

    protected void GVNatResults_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        ////Check the row type is header 
        if (e.Row.RowType == DataControlRowType.Header)
        {
            foreach (TableCell cell in e.Row.Cells)
            {
                foreach (Control ctrl in cell.Controls)
                {
                    if (ctrl.GetType().ToString().Contains("DataControlLinkButton"))
                    {
                        if (((LinkButton)ctrl).Text == "IUCN")
                        {
                            cell.Attributes.Add("title", "Category Ia: Strict Nature Reserve; Category Ib: Wilderness Area; Category II: National Park; Category III: Natural Monument; Category IV: Habitat/Species Management Area; Category V: Protected Landscape/Seascape; Category VI: Managed Resource Protected Area");
                        }
                        else if (((LinkButton)ctrl).Text == "Marine or Terrestrial")
                        {
                            cell.Attributes.Add("title", "Both - Includes Terrestrial Protected Areas indicated as having a Marine element.");
                        }
                    }
                }
            }
        }
    }

    protected void GVIntResults_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            foreach (TableCell cell in e.Row.Cells)
            {
                foreach (Control ctrl in cell.Controls)
                {
                    if (ctrl.GetType().ToString().Contains("DataControlLinkButton"))
                    {
                        if (((LinkButton)ctrl).Text == "Marine or Terrestrial")
                        {
                            cell.Attributes.Add("title", "Both - Includes Terrestrial Protected Areas indicated as having a Marine element.");
                        }
                    }
                }
            }
        }
    }

    private void Page_Error(Object sender, EventArgs e)
    {
        if (m_LogFilePath == null)
        {
            //m_myLogging.WriteToLog(true);
        }
    }
}
